﻿Imports System.Data.OleDb
Public Class HocSinhDAO
    Public Shared Function SelectAll() As List(Of HocSinhDTO)
        Const strSql As String = "select MAHOCSINH, TENHOCSINH, GIOITINH, NGAYSINH, DIACHI, SODT, EMAIL, MALOP from HOCSINH"
        Dim ds = New List(Of HocSinhDTO)
        Using db = New SQLProvider()
            Using re = db.ExecuteReader(strSql)
                While re.Read()
                    Dim obj = New HocSinhDTO
                    obj.MAHOCSINH = re.GetInt32(0)
                    obj.TENHOCSINH = re.GetString(1)
                    obj.GIOITINH = re.GetString(2)
                    obj.NGAYSINH = re.GetString(3)
                    obj.DIACHI = re.GetString(4)
                    obj.SODT = re.GetString(5)
                    obj.EMAIL = re.GetString(6)
                    obj.MALOP = re.GetInt32(7)
                    ds.Add(obj)
                End While
            End Using
        End Using

        Return ds
    End Function
    'Public Shared Function SelectAll1() As List(Of HocSinhDTO)
    '    Const strSql As String = "select * from LOP"
    '    Dim ds1 = New List(Of HocSinhDTO)
    '    Using db1 = New SQLProvider()
    '        Using re1 = db1.ExecuteReader(strSql)
    '            While re1.Read()
    '                Dim obj1 = New HocSinhDTO
    '                obj1.MALOP1 = re1.GetString(0)
    '            End While
    '        End Using
    '    End Using

    '    Return ds1
    'End Function
    Public Shared Function SelectByMaLop(ByVal MaLop As Integer) As List(Of HocSinhDTO)
        Dim strSql As String = String.Format("select MAHOCSINH,TENHOCSINH, GIOITINH,NGAYSINH,DIACHI,SODT,EMAIL,MALOP from HOCSINH where MaLop = {0}", MaLop)
        Dim ds = New List(Of HocSinhDTO)
        Using db = New SQLProvider()
            Using re = db.ExecuteReader(strSql)
                While re.Read()
                    Dim obj = New HocSinhDTO
                    'obj.MALOP = re.GetString(0)
                    obj.MAHOCSINH = re.GetInt32(0)
                    obj.TENHOCSINH = re.GetString(1)
                    obj.GIOITINH = re.GetString(2)
                    obj.NGAYSINH = re.GetString(3)
                    obj.DIACHI = re.GetString(4)
                    obj.SODT = re.GetString(5)
                    obj.EMAIL = re.GetString(6)
                    obj.MALOP = re.GetInt32(7)
                    ds.Add(obj)
                End While
            End Using
        End Using

        Return ds
    End Function
    Public Shared Function SelectByKey(ByVal MaCN As Integer) As HocSinhDTO
        Dim strSql As String = String.Format("select MaHS, TenHS, GioiTinh, Cmnd ,NgaySinh,DiaChi,  MaCN   from ChiNhanh where MaCN = '{0}'", MaCN)

        Using db = New SQLProvider()
            Using re = db.ExecuteReader(strSql)
                If re.Read() Then
                    Dim obj = New HocSinhDTO
                    obj.MAHOCSINH = re.GetInt32(0)
                    obj.TENHOCSINH = re.GetString(1)
                    obj.GIOITINH = re.GetString(2)
                    obj.NGAYSINH = re.GetDateTime(3)
                    obj.DIACHI = re.GetString(4)
                    obj.SODT = re.GetString(5)
                    obj.EMAIL = re.GetString(6)
                    obj.MALOP = re.GetString(7)
                    Return obj
                End If

            End Using
        End Using
        Return Nothing

    End Function
    Public Shared Function LayDSHocSinh() As List(Of HocSinhDTO)
        Dim str = "select * from HOCSINH"
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of HocSinhDTO)

        While reader.Read()
            Dim hs As New HocSinhDTO
            hs.MAHOCSINH = reader.GetInt32(0)
            hs.TENHOCSINH = reader.GetString(1)
            hs.GIOITINH = reader.GetString(2)
            hs.NGAYSINH = reader.GetString(3)
            hs.DIACHI = reader.GetString(4)
            hs.SODT = reader.GetString(5)
            hs.EMAIL = reader.GetString(6)
            hs.MALOP = reader.GetInt32(7)

            ds.Add(hs)
        End While
        conn.Close()
        Return ds
    End Function

    Public Shared Function Them(ByVal cn As HocSinhDTO) As Integer
        Using db = New SQLProvider

            Dim strSql = String.Format("Insert into HOCSINH ( MAHOCSINH,TENHOCSINH, GIOITINH,NGAYSINH,DIACHI,SODT,EMAIL,MALOP) values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}','{6}','{7}')", _
                                             cn.MAHOCSINH, cn.TENHOCSINH, cn.GIOITINH, cn.NGAYSINH, cn.DIACHI, cn.SODT, cn.EMAIL, cn.MALOP)

            Return db.ExecNoneQuery(strSql)
        End Using
    End Function
    'Public Shared Function Sua(ByVal cn As HocSinhDTO) As Integer
    '    Using db = New SQLProvider

    '        Dim strSql = String.Format("Update HOCSINH set MAHOCSINH = '{0}, TENHOCSINH = '{1}', GIOITINH = '{2}', NGAYSINH = '{3}', DIACHI = '{4}', SODT = '{5}', EMAIL = '{6}'  where MALOP = '{7}'", _
    '                                          cn.MAHOCSINH, cn.TENHOCSINH, cn.GIOITINH, cn.NGAYSINH, cn.DIACHI, cn.SODT, cn.EMAIL, cn.MALOP)

    '        Return db.ExecNoneQuery(strSql)
    '    End Using
    'End Function
    Public Shared Function SuaKhachHang(ByVal hs As HocSinhDTO) As Integer
        Dim str = String.Format("update HOCSINH set TENHOCSINH='{0}', GIOITINH='{1}', NGAYSINH='{2}', DIACHI='{3}', SODT='{4}', EMAIL='{5}',MALOP ={6} where MAHOCSINH={7}", hs.TENHOCSINH, hs.GIOITINH, hs.NGAYSINH, hs.DIACHI, hs.SODT, hs.EMAIL, hs.MALOP, hs.MAHOCSINH)
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(str, conn)
        Dim result = -1
        result = cmd.ExecuteNonQuery()
        conn.Close()
        Return result
    End Function
    'Public Shared Function Xoa(ByVal MAHOCSINH As Integer) As Integer
    '    Using db = New SQLProvider

    '        Dim strSql = String.Format("Delete HOCSINH where MAHOCSINH = '{0}'", _
    '                                        MAHOCSINH)

    '        Return db.ExecNoneQuery(strSql)
    '    End Using
    'End Function

    Public Shared Function XoaKhachHang(ByVal hs As HocSinhDTO) As Integer
        Dim str = String.Format("delete from HOCSINH where MAHOCSINH={0}", hs.MAHOCSINH)
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(str, conn)
        Dim result = -1
        result = cmd.ExecuteNonQuery()
        conn.Close()
        Return result
    End Function
End Class
